﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DLL
{
    public class DAInvestor
    {
        // Chuỗi kết nối
        string strConnection;

        // SqlConnection
        SqlConnection sqlConn = null;
        private StringBuilder sbCommand = new StringBuilder(String.Empty);

        // Ten bang
        private const string c_table = "investor";

        // Ten cot cua bang Chu Dau Tu
        private const string c_id = "investor_id";
        private const string c_name = "investor_name";
        private const string c_address = "investor_address";

        public DAInvestor(string strConnection)
        {
            this.strConnection = strConnection;
        }

        private string BuildInsertString(BLL.BOInvestor investor)
        {
            sbCommand = new StringBuilder(String.Empty);
            sbCommand.AppendFormat("INSERT INTO {0}", c_table).AppendLine();
            sbCommand.AppendFormat("({0},", c_name).AppendLine();
            sbCommand.AppendFormat("{0})", c_address).AppendLine();
            sbCommand.AppendFormat("VALUES(N'{0}', N'{1}')", investor.Investor_name, investor.Invester_address).AppendLine();

            return sbCommand.ToString();
        }

        private string BuildUpdateString(BLL.BOInvestor investor)
        {
            sbCommand = new StringBuilder(String.Empty);
            sbCommand.AppendFormat("UPDATE {0}", c_table).AppendLine();
            sbCommand.AppendFormat("SET {0} = N'{1}',", c_name, investor.Investor_name).AppendLine();
            sbCommand.AppendFormat("{0} =  N'{1}'", c_address, investor.Invester_address).AppendLine();
            sbCommand.AppendFormat("WHERE {0} =  N'{1}'", c_id, investor.Investor_id).AppendLine();

            return sbCommand.ToString();
        }

        private void OpenConnection()
        {
            sqlConn = new SqlConnection(strConnection);
            sqlConn.Open();
        }

        private void CloseConnection()
        {
            sqlConn.Close();
        }

        private bool IsExistInvestor(BLL.BOInvestor investor, string strConnection)
        {
            sbCommand = new StringBuilder(String.Empty);
            string investor_name = investor.Investor_name.ToString();
            sbCommand.AppendFormat("SELECT * FROM {0} WHERE {1} = @investor_name", c_table, c_name).AppendLine();
            SqlConnection sqlConnection = new SqlConnection(strConnection);
            SqlDataAdapter da = new SqlDataAdapter(sbCommand.ToString(), sqlConnection);
            da.SelectCommand.Parameters.AddWithValue("@investor_name", investor_name);
            DataTable dataTable = new DataTable();
            da.Fill(dataTable);

            if (dataTable.Rows.Count < 1)
            {
                // Chu Dau Tu chua co
                return false;
            }
            else
            {
                // Chu Dau Tu da ton tai
                return true;
            }
        }

        public bool CreateInvestor(BLL.BOInvestor investor, string strConnection)
        {

            string strINSERT = this.BuildInsertString(investor);

            // Kiem tra chu dau tu da ton tai trong co so du lieu chua
            if (IsExistInvestor(investor, strConnection))
            {
                return false;
            }
            else
            {
                SqlConnection sqlConnection = new SqlConnection(strConnection);
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand(strINSERT, sqlConnection);

                // execute Query
                sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
                return true;
            }
        }

        public DataSet GetInvestors(string strInvestorName)
        {
            DataSet ds = new DataSet();
            this.OpenConnection();
            sbCommand = new StringBuilder(String.Empty);
            sbCommand.AppendFormat("SELECT  * FROM {0} IVT", c_table).AppendLine();
            sbCommand.AppendFormat("WHERE IVT.investor_name LIKE N'%{0}%'", strInvestorName).AppendLine();
            SqlCommand cmd = new SqlCommand(sbCommand.ToString(), sqlConn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            this.CloseConnection();
            return ds;
        }

        public DataSet GetInvestorsByID(int investor_id)
        {
            DataSet ds = new DataSet();
            this.OpenConnection();
            sbCommand = new StringBuilder(String.Empty);
            sbCommand.AppendFormat("SELECT  * FROM {0} IVT", c_table).AppendLine();
            sbCommand.AppendFormat("WHERE IVT.investor_id =N'{0}'", investor_id).AppendLine();
            SqlCommand cmd = new SqlCommand(sbCommand.ToString(), sqlConn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            this.CloseConnection();
            return ds;
        }

        public int GetMaxInvestorID()
        {
            int maxID = 0;
            DataTable dt = new DataTable();
            this.OpenConnection();
            sbCommand = new StringBuilder(String.Empty);
            sbCommand.AppendFormat("SELECT  Max(investor_id) maxid FROM {0} IVT", c_table).AppendLine();
            SqlCommand cmd = new SqlCommand(sbCommand.ToString(), sqlConn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            if (dt.Rows.Count == 1)
            {
                DataRow DR;
                DR = dt.Rows[0];
                maxID = Convert.ToInt32(DR["maxid"]);
            }
            return maxID;
        }

        public int GetInvestorID(string strInvestorName)
        {
            DataTable dt = new DataTable();
            int investor_id = 0;
            this.OpenConnection();
            sbCommand = new StringBuilder(String.Empty);
            sbCommand.AppendFormat("SELECT  investor_id FROM {0} IVT", c_table).AppendLine();
            sbCommand.AppendFormat("WHERE IVT.investor_name =N'{0}'", strInvestorName).AppendLine();
            SqlCommand cmd = new SqlCommand(sbCommand.ToString(), sqlConn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            if (dt.Rows.Count == 1)
            {
                DataRow DR;
                DR = dt.Rows[0];
                investor_id = Convert.ToInt32(DR["investor_id"]);
            }
            else
            {
                investor_id = this.GetMaxInvestorID() + 1;
            }

            this.CloseConnection();

            return investor_id;
        }

        public void UpdateInvestor(BLL.BOInvestor investor, string strConnection)
        {

            string strUpdate = this.BuildUpdateString(investor);


            try
            {
                SqlConnection sqlConnection = new SqlConnection(strConnection);
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand(strUpdate, sqlConnection);

                // execute Query
                sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message.ToString());
            }
        }
    }
}
